use serde::{Deserialize, Serialize};
use sqlx::sqlite::SqlitePool;
use sqlx::Done;

// use chrono::naive::serde::ts_seconds;
use chrono::prelude::*;

#[derive(sqlx::FromRow, Debug, Deserialize, Serialize)]
pub struct Article {
    pub id: i64,
    pub content: String,
    pub title: String,

    // #[serde(with = "ts_seconds")]
    pub created_at: NaiveDateTime,
    // #[serde(with = "ts_seconds")]
    pub updated_at: NaiveDateTime,
}

#[derive(Debug, Deserialize, Serialize, Default)]
pub struct PartialArticle {
    pub content: Option<String>,
    pub title: Option<String>,
}

impl PartialArticle {
    pub async fn update_by_id(&self, pool: &SqlitePool, id: i64) -> anyhow::Result<bool> {
        let s = self.clone();
        let now = Local::now().naive_local();

        let rows_affected = sqlx::query!(
            "update articles set content=?, title=?, updated_at=? where id=?",
            s.content,
            s.title,
            now,
            id
        )
        .execute(pool)
        .await?
        .rows_affected();

        Ok(rows_affected > 0)
    }

    pub async fn create(&self, pool: &SqlitePool) -> anyhow::Result<i64> {
        // sqlite 。。。
        let mut conn = pool.acquire().await?;
        let now = Local::now().naive_local();

        let s = self.clone();
        let insert_id = sqlx::query!(
            r#"INSERT INTO articles (content, title, created_at, updated_at ) VALUES (?, ?, ?, ?)"#,
            s.content,
            s.title,
            now,
            now
        )
        .execute(&mut conn)
        .await?
        .last_insert_rowid();
        // .last_insert_id(); // mysql

        Ok(insert_id)
    }
}

impl Article {
    pub async fn all(pool: &SqlitePool) -> anyhow::Result<Vec<Article>> {
        // let time1 = Local::now() - chrono::Duration::minutes(10);

        let list: Vec<Article> =
            sqlx::query_as!(Article, "SELECT * FROM articles order by id desc",)
                .fetch_all(pool)
                .await?;

        Ok(list)
    }

    // pub async fn last_id(pool: &SqlitePool) -> anyhow::Result<u64> {
    //     let last_id = sqlx::query_as!(u64, "SELECT last_insert_rowid()")
    //         .fetch_one(pool)
    //         .await?;

    //     Ok(last_id)
    // }

    pub async fn find_by_id(pool: &SqlitePool, id: i64) -> anyhow::Result<Article> {
        let article: Article = sqlx::query_as!(Article, "SELECT * FROM articles WHERE id = ?", id)
            .fetch_one(pool)
            .await?;

        Ok(article)
    }

    pub async fn delete_by_id(pool: &SqlitePool, id: i64) -> anyhow::Result<bool> {
        let rows_affected: u64 = sqlx::query!("DELETE FROM articles WHERE id = $1", id)
            .execute(pool)
            .await?
            .rows_affected();

        Ok(rows_affected > 0)
    }

    // pub fn update(&self, partial: PartialArticle) -> Query {
    //     partial.update_by_id(self.id)
    // }
}
